import pymysql


#链接数据库
def mycoon():
    try:
        c1 = pymysql.connect(user="root",
                             password='123456',
                             database='db_test',
                             host='127.0.0.1',
                             port=3306,
                             charset='utf8')
        print('---连接成功---')
        return c1
    except Exception:
        print('---连接失败---')
mycoon()
#执行操作语句并返回结果
def myexecute(c1, sql):
    try:
        c2 = c1.cursor()
        c2.execute(sql)
        c1.commit()
        c3 = c2.fetchall()
        print(c3)
    except Exception:
        print('---数据库执行失败---')

#关闭链接
def myclose(c1):
    try:
        # 链接关闭
        c1.close()
    except Exception as x:
        print(x)
        print("----数据库关闭失败----")

#创建数据库中表的函数，其中封装了链接数据库，执行命令以及关闭链接的三个步骤
def createtable():
    # 链接数据库
    c1 = mycoon()
    sql = "create table employee(id int,name char(100),sex char(3),address char(200))"
    # 执行命令
    myexecute(c1, sql)
    # 关闭
    myclose(c1)


def insert(sql):
    c1 = mycoon()
    myexecute(c1, sql)
    myclose(c1)


def delete():
    c1 = mycoon()
    sql = "delete from excel where id=2"
    myexecute(c1, sql)
    myclose(c1)


def update(sql):
    c1 = mycoon()
    myexecute(c1, sql)
    myclose(c1)

def select(sql):
    c1 = mycoon()
    myexecute(c1, sql)
    myclose(c1)


if __name__ == '__main__':
    # createtable()

    import faker
    f = faker.Faker(locale="zh-CN")
    info = f.profile()
    sql = f"insert into employee values(1,'{info.get("name")}','{info.get("sex")}','{info.get("address")}')"
    insert(sql)
    sql1="update excel set age=20 where name='tom'"
    delete()
    update(sql1)
    sql2="select * from excel"
    select(sql2)